package com.wmx.jdbc_template_app.controller;

import cn.hutool.core.date.DateUtil;
import com.wmx.jdbc_template_app.pojo.Person;
import com.wmx.jdbc_template_app.utils.JdbcUtil;
import com.wmx.jdbc_template_app.utils.ResultCode;
import com.wmx.jdbc_template_app.utils.ResultData;
import oracle.jdbc.OracleTypes;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.*;

import java.sql.*;
import java.util.*;

/**
 * 原生 JDBC 代码 增删改查 操作演示
 * 可以参考{@link JdbcUtil}
 *
 * @author wangMaoXiong
 * @version 1.0
 * @date 2022/4/17 15:29
 */
@RestController
@SuppressWarnings("Duplicates")
public class OriginalJdbcController {

    private static final Logger log = LoggerFactory.getLogger(OriginalJdbcController.class);

    /**
     * 原生 JDBC 查询
     * http://localhost:8080/jdbc/queryAll/5
     *
     * @param limit
     * @return
     * @throws Exception
     */
    @GetMapping("/jdbc/queryAll/{limit}")
    @SuppressWarnings("Duplicates")
    public List<Person> queryAll(@PathVariable int limit) throws Exception {
        List<Person> personList;
        Connection conn = null;
        ResultSet resultSet = null;
        PreparedStatement prepareStatement = null;
        try {
            // 1、注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2、获取连接
            String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);

            // 3、获取预编译SQL语句对象
            prepareStatement = conn.prepareStatement("select * from person limit ? ");
            prepareStatement.setInt(1, limit);

            // 4、执行SQL操作，查询操作会返回结果集(相当于一个游标)
            resultSet = prepareStatement.executeQuery();

            // 5、获取结果集
            personList = new ArrayList<>(limit);
            while (resultSet.next()) {
                Person person = new Person();
                person.setpId(resultSet.getInt("pId"));
                person.setpName(resultSet.getString("pName"));
                person.setBirthday(resultSet.getDate("birthday"));
                person.setSalary(resultSet.getFloat("salary"));
                person.setSummary(resultSet.getString("summary"));
                personList.add(person);
            }
            // 6、关闭连接，释放资源
            prepareStatement.close();
            conn.close();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
            if (prepareStatement != null && !prepareStatement.isClosed()) {
                try {
                    prepareStatement.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
            if (conn != null && !conn.isClosed()) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
        }
        return personList;
    }

    /**
     * 原生 JDBC 查询————模糊查下
     * http://localhost:8080/jdbc/queryAll2?keyword=xxx
     *
     * @param keyword
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbc/queryAll2")
    @SuppressWarnings("Duplicates")
    public List<Map<String, Object>> queryAll2(String keyword) throws SQLException {
        List<Map<String, Object>> mapList = null;
        PreparedStatement prepareStatement = null;
        Connection conn = null;
        try {
            // 1、注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2、获取连接
            String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);

            // 3、获取预编译SQL语句对象
            String sql = "select * from person ";
            if (keyword != null && keyword.trim().length() > 0) {
                sql += " where pname like ? or summary like ?";
            }
            prepareStatement = conn.prepareStatement(sql);
            if (keyword != null && keyword.trim().length() > 0) {
                prepareStatement.setString(1, "%" + keyword + "%");
                prepareStatement.setString(2, "%" + keyword + "%");
            }

            // 4、执行SQL操作，查询操作会返回结果集(相当于一个游标)
            ResultSet resultSet = prepareStatement.executeQuery();

            // 5、获取结果集
            mapList = resultSetToList(resultSet);

            // 6、关闭连接，释放资源
            prepareStatement.close();
            conn.close();
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
        } finally {
            if (prepareStatement != null && !prepareStatement.isClosed()) {
                try {
                    prepareStatement.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
            if (conn != null && !conn.isClosed()) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
        }
        return mapList;
    }

    /**
     * 原生 JDBC 更新(包括但是不限于 CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、GRANT、REVOKE)
     * http://localhost:8080/jdbc/executeUpdate
     * <p>
     * {"pName":"张三","salary":1899.0,"birthday":"2022-04-05","summary":"游客"}
     *
     * @param person
     * @return
     * @throws Exception
     */
    @PostMapping("/jdbc/executeUpdate")
    @SuppressWarnings("Duplicates")
    public int executeUpdate(@RequestBody Person person) throws SQLException {
        int count = 0;
        Connection conn = null;
        PreparedStatement prepareStatement = null;
        try {
            // 1、注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2、获取连接
            String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);

            // 关闭事务自动提交，改为手动提交
            conn.setAutoCommit(false);

            // 3、获取预编译SQL语句对象
            String sql = "INSERT INTO PERSON(pId, pName,birthday,salary,summary) VALUES (null,?,?,?,?)";
            prepareStatement = conn.prepareStatement(sql);
            // 设置占位符参数的值(索引从1开始，表示第几个占位符)
            // 如果嫌麻烦，直接使用 setObject 赋值也可以，前提是赋值的值的类型能自动转换成功。java.sql.Date
            prepareStatement.setString(1, person.getpName());
            // 使用Timestamp，防止丢失时分秒
            // prepareStatement.setDate(2, new Date(person.getBirthday().getTime()));
            prepareStatement.setTimestamp(2, new Timestamp(person.getBirthday().getTime()));
            prepareStatement.setFloat(3, person.getSalary());
            prepareStatement.setString(4, person.getSummary());

            // 4、执行SQL操作，返回成功插入的条数
            // prepareStatement.execute();
            count = prepareStatement.executeUpdate();

            // 提交事务
            conn.commit();
        } catch (Exception e) {
            if (conn != null) {
                // 回滚事务
                conn.rollback();
            }
            log.error(ExceptionUtils.getStackTrace(e));
        } finally {
            // 6、关闭连接，释放资源
            if (prepareStatement != null && !prepareStatement.isClosed()) {
                try {
                    prepareStatement.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
            if (conn != null && !conn.isClosed()) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
        }
        return count;
    }

    /**
     * 批量新增
     * http://localhost:8080/jdbc/batchInsert?batchSize=20
     *
     * @param batchSize ：批量新增的条数
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbc/batchInsert")
    @SuppressWarnings("Duplicates")
    public ResultData batchInsert(@RequestParam Integer batchSize) throws SQLException {
        Connection conn = null;
        PreparedStatement prepareStatement = null;
        batchSize = batchSize == null || batchSize <= 0 ? 100 : batchSize > 10000 ? 10000 : batchSize;
        try {
            // 1、注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2、获取连接
            String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);

            // 关闭事务自动提交，改为手动提交(防止部分成功，部分失败)
            conn.setAutoCommit(false);

            // 封装参数(需要批量新增的数据)
            List<Map<String, Object>> argList = new ArrayList<>(batchSize);
            Map<String, Object> argMap;
            for (int i = 0; i < batchSize; i++) {
                argMap = new HashMap<>(8);
                argMap.put("pName", "批量新增" + (i + 1));
                // 使用Timestamp，防止丢失时分秒
                // argMap.put("birthday", new Date(new java.util.Date().getTime()));
                argMap.put("birthday", new Timestamp(System.currentTimeMillis()));
                argMap.put("salary", 100 + new Random().nextFloat() * 100000);
                argMap.put("summary", "自动新增");
                argList.add(argMap);
            }
            // 3、获取预编译SQL语句对象
            String sql = "INSERT INTO PERSON(pId,pName,birthday,salary,summary) VALUES (null,?,?,?,?)";
            prepareStatement = conn.prepareStatement(sql);

            // 设置占位符参数的值(索引从1开始，表示第几个占位符)
            for (Map<String, Object> argLoopMap : argList) {
                prepareStatement.setObject(1, argLoopMap.get("pName"));
                prepareStatement.setObject(2, argLoopMap.get("birthday"));
                prepareStatement.setObject(3, argLoopMap.get("salary"));
                prepareStatement.setObject(4, argLoopMap.get("summary"));
                // 积攒sql(虽然循环设置的参数，但是没有关系，它会自动处理成sql)
                prepareStatement.addBatch();
            }
            // 4、批量执行SQL操作，返回成功插入的条数
            // [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1}
            int[] ints = prepareStatement.executeBatch();
            // 清除积攒的Sql与参数
            prepareStatement.clearBatch();
            // 提交事务
            conn.commit();
            return ResultData.success(ints);
        } catch (Exception e) {
           if (conn != null) {
               // 回滚事务
               conn.rollback();
           }
            log.error(ExceptionUtils.getStackTrace(e));
            return ResultData.error(ResultCode.FAIL, e);
        } finally {
            // 6、关闭连接，释放资源
            if (prepareStatement != null && !prepareStatement.isClosed()) {
                try {
                    prepareStatement.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
            if (conn != null && !conn.isClosed()) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
        }
    }

    /**
     * 批量修改
     * http://localhost:8080/jdbc/batchUpdate?startPid=20&endPid=30
     *
     * @param startPid
     * @param endPid
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbc/batchUpdate")
    @SuppressWarnings("Duplicates")
    public ResultData batchUpdate(@RequestParam Integer startPid,
                                  @RequestParam Integer endPid) throws SQLException {
        Connection conn = null;
        PreparedStatement prepareStatement = null;
        try {
            // 1、注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2、获取连接
            String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);

            // 关闭事务自动提交，改为手动提交(防止部分成功，部分失败)
            conn.setAutoCommit(false);

            // 封装参数(需要批量新增的数据)
            List<Map<String, Object>> argList = new ArrayList<>(endPid - startPid);
            Map<String, Object> argMap;
            for (int i = startPid; i <= endPid; i++) {
                argMap = new HashMap<>(8);
                argMap.put("pId", i);
                argMap.put("pName", "批量修改" + i);
                // 使用Timestamp，防止丢失时分秒
                // argMap.put("birthday", new Date(DateUtil.parseDate("1993/08/25").getTime() + i));
                argMap.put("birthday", new Timestamp(DateUtil.parseDateTime("1993/08/25 15:30:30").getTime()));
                argMap.put("salary", 2000 + new Random().nextFloat() * 100000);
                argMap.put("summary", "批量修改" + DateUtil.now());
                argList.add(argMap);
            }
            // 3、获取预编译SQL语句对象
            String sql = "update PERSON set pName=?,birthday=?,salary=?,summary=? where pId=?";
            prepareStatement = conn.prepareStatement(sql);

            // 设置占位符参数的值(索引从1开始，表示第几个占位符)
            for (Map<String, Object> argLoopMap : argList) {
                prepareStatement.setObject(1, argLoopMap.get("pName"));
                prepareStatement.setObject(2, argLoopMap.get("birthday"));
                prepareStatement.setObject(3, argLoopMap.get("salary"));
                prepareStatement.setObject(4, argLoopMap.get("summary"));
                prepareStatement.setObject(5, argLoopMap.get("pId"));
                // 积攒sql(虽然循环设置的参数，但是没有关系，它会自动处理成sql)
                prepareStatement.addBatch();
            }
            // 4、批量执行SQL操作，返回成功插入的条数
            // [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1}
            int[] ints = prepareStatement.executeBatch();
            // 清除积攒的Sql与参数
            prepareStatement.clearBatch();
            // 提交事务
            conn.commit();
            return ResultData.success(ints);
        } catch (Exception e) {
            if (conn != null) {
                // 回滚事务
                conn.rollback();
            }
            log.error(ExceptionUtils.getStackTrace(e));
            return ResultData.error(ResultCode.FAIL, e);
        } finally {
            // 6、关闭连接，释放资源
            if (prepareStatement != null && !prepareStatement.isClosed()) {
                try {
                    prepareStatement.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
            if (conn != null && !conn.isClosed()) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
        }
    }

    // =======================JdbcUtil==./start===============================

    /**
     * 获取数据库版本
     * http://localhost:8080/jdbcUtil/getDatabaseProduct
     *
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/getDatabaseProduct")
    public ResultData getDatabaseProduct() throws SQLException {
        return ResultData.success(JdbcUtil.getDatabaseProduct());
    }

    /**
     * http://localhost:8080/jdbcUtil/getOne1?empNo=7788
     *
     * @param empNo
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/getOne1")
    public ResultData getOne1(@RequestParam int empNo) throws SQLException {
        String sql = "SELECT * FROM emp t WHERE empno = '" + empNo + "'";
        Map<String, Object> getMap = JdbcUtil.getOne(sql);
        return ResultData.success(getMap);
    }

    /**
     * http://localhost:8080/jdbcUtil/getOne2?deptNo=20
     *
     * @param deptNo
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/getOne2")
    public ResultData getOne2(@RequestParam int deptNo) throws SQLException {
        String sql = "SELECT * FROM dept t WHERE deptno = ?";
        Object[] args = {deptNo};
        Map<String, Object> getMap = JdbcUtil.getOne(sql, args);
        return ResultData.success(getMap);
    }

    /**
     * http://localhost:8080/jdbcUtil/queryForList1
     *
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/queryForList1")
    public ResultData queryForList1() throws SQLException {
        String sql = "SELECT * FROM dept";
        List<Map<String, Object>> mapList = JdbcUtil.queryForList(sql);
        return ResultData.success(mapList);
    }

    /**
     * http://localhost:8080/jdbcUtil/queryForList2?pageNo=1&pageSize=4
     *
     * @param pageNo   ：页码，从1开始
     * @param pageSize ：没页展示的条数
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/queryForList2")
    public ResultData queryForList2(@RequestParam(defaultValue = "1") int pageNo,
                                    @RequestParam(defaultValue = "10") int pageSize) throws SQLException {
        // 强制每页不超过1000条
        pageSize = pageSize > 1000 ? 1000 : pageSize;
        int startIndex = ((pageNo - 1) * pageSize + 1);
        int endIndex = pageNo * pageSize;
        String sql = "SELECT * FROM (SELECT ROWNUM rr_, tt2.* FROM (SELECT * FROM emp ORDER BY ename,empno) tt2 WHERE rownum <= ?) WHERE rr_ >= ?";
        Object[] args = {endIndex, startIndex};
        List<Map<String, Object>> mapList = JdbcUtil.queryForList(sql, args);
        return ResultData.success(mapList);
    }

    /**
     * http://localhost:8080/jdbcUtil/pageList?pageNo=1&pageSize=4
     *
     * @param pageNo   ：页码，从1开始
     * @param pageSize ：没页展示的条数
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/pageList")
    public ResultData pageList(@RequestParam(defaultValue = "1") int pageNo,
                               @RequestParam(defaultValue = "10") int pageSize) throws SQLException {
        // 强制每页不超过1000条
        pageSize = pageSize > 1000 ? 1000 : pageSize;
        String sql = "SELECT * FROM emp ORDER BY ename,empno";
        List<Map<String, Object>> mapList = JdbcUtil.pageList(sql, null, pageNo, pageSize);
        return ResultData.success(mapList);
    }

    /**
     * 获取表或者视图列的完整信息：包括 列名、类型、长度、小数位数、是否允许为空 等等。
     * http://localhost:8080/jdbcUtil/getColumnFullAttrs?objName=dept
     * http://localhost:8080/jdbcUtil/getColumnFullAttrs?objName=emp
     *
     * @param objName ：表名称、视图名。如 emp、vw_emp
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/getColumnFullAttrs")
    public ResultData getColumnFullAttrs(@RequestParam String objName) throws SQLException {
        List<Map<String, Object>> columns = JdbcUtil.getColumnFullAttrs(objName);
        return ResultData.success(columns);
    }

    /**
     * 获取全部数据库名称(Oracle数据库时返回的全部用户名称)
     * http://localhost:8080/jdbcUtil/getAllDatabases
     *
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/getAllDatabases")
    public ResultData getAllDatabases() throws SQLException {
        List<Map<String, Object>> allDatabases = JdbcUtil.getAllDatabases();
        return ResultData.success(allDatabases);
    }

    /**
     * 获取指定数据库或者用户下面的表与视图
     * http://localhost:8080/jdbcUtil/getTablesByDatabase?dbName=scott
     *
     * @param dbName
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/getTablesByDatabase")
    public ResultData getTablesByDatabase(@RequestParam String dbName) throws SQLException {
        List<Map<String, Object>> allDatabases = JdbcUtil.getTablesByDatabase(dbName);
        return ResultData.success(allDatabases);
    }

    /**
     * 获取表或者视图列的完整信息：包括 列名、类型、长度、小数位数、是否允许为空 等等。
     * http://localhost:8080/jdbcUtil/getColumns?tableName=emp
     *
     * @param tableName ：表名称、视图名。如 emp、vw_emp
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/getColumns")
    public ResultData getColumns(@RequestParam String tableName) throws SQLException {
        List<Map<String, Object>> allDatabases = JdbcUtil.getColumns(tableName);
        return ResultData.success(allDatabases);
    }

    /**
     * 获取表的主键信息
     * http://localhost:8080/jdbcUtil/getPrimaryKeys?tableName=emp
     *
     * @param tableName
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/getPrimaryKeys")
    public ResultData getPrimaryKeys(@RequestParam String tableName) throws SQLException {
        List<Map<String, Object>> allDatabases = JdbcUtil.getPrimaryKeys(tableName);
        return ResultData.success(allDatabases);
    }

    /**
     * 获取表的外键信息
     * http://localhost:8080/jdbcUtil/getForeignKeys?tableName=emp
     *
     * @param tableName
     * @return
     * @throws SQLException
     */
    @GetMapping("/jdbcUtil/getForeignKeys")
    public ResultData getForeignKeys(@RequestParam String tableName) throws SQLException {
        List<Map<String, Object>> allDatabases = JdbcUtil.getForeignKeys(tableName);
        return ResultData.success(allDatabases);
    }
    // =======================JdbcUtil==./end===============================


    /**
     * Oracle + 存储过程 + 引用游标 + 原生 Java JDBC ：分页查询员工信息
     * http://localhost:8080/jdbc/proQueryEmpLimit
     * http://localhost:8080/jdbc/proQueryEmpLimit?pageNo=1&pageSize=20
     *
     * @param pageNo   ：查询的页码
     * @param pageSize ：每页的条数
     * @return
     */
    @GetMapping("/jdbc/proQueryEmpLimit")
    public ResultData proQueryEmpLimit(@RequestParam(defaultValue = "1") int pageNo,
                                       @RequestParam(defaultValue = "10") int pageSize) {
        Connection conn = null;
        CallableStatement callableStatement = null;
        ResultSet resultSet = null;
        ResultData<List<Map<String, Object>>> resultData;
        try {
            //1、注册驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            String username = "scott";
            String password = "scott";
            // 2、获取连接
            conn = DriverManager.getConnection(url, username, password);

            // 3、设置请求参数与注册返回值参数
            // 存储过程 sql 格式为：{call <procedure-name>[(<arg1>,<arg2>, ...)]}，arg1、arg2...参数使用占位符
            // 占位符索引从1开始，最后一个参数是返回的游标
            // 存储过程sql源码在：src\main\resources\data\pro_query_emp_limit.sql
            String sql = "{call pro_query_emp_limit(?,?,?)}";
            callableStatement = conn.prepareCall(sql);
            // 设置输入参数，pageNo 查询的页码，pageSize 每页的条数
            callableStatement.setInt(1, pageNo);
            callableStatement.setInt(2, pageSize);
            // 注册输出参数类型，使用游标解释结果集
            callableStatement.registerOutParameter(3, OracleTypes.CURSOR);

            // 4、执行statement
            callableStatement.execute();

            // 5、取出结果集
            List<Map<String, Object>> dataList = new ArrayList<>();
            // 下面两种方式都可以获取结果集，getObject 方式兼容性更好
            // resultSet = ((oracle.jdbc.OracleCallableStatement) callableStatement).getCursor(3);
            resultSet = (ResultSet) callableStatement.getObject(3);
            // 结果集元数据对象，可以用于或者表的列信息，即使没有查询到值，列信息同样会有
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> rowData = new HashMap(columnCount);
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(resultSetMetaData.getColumnName(i), resultSet.getObject(i));
                }
                dataList.add(rowData);
            }
            resultData = ResultData.success(dataList);
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            resultData = ResultData.error(ResultCode.FAIL, e);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
            if (callableStatement != null) {
                try {
                    callableStatement.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                }
            }
        }
        return resultData;
    }


    /**
     * 将 resultSetToList 结果集转为 List<Map<String, Object>>
     *
     * @param resultSet ：没有查询到值时，不会为 null，只是游标 next 不会走，
     * @return
     * @throws SQLException
     */
    private List<Map<String, Object>> resultSetToList(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<>();
        // 结果集元数据对象，可以用于获取表的列信息，即使没有查询到值，列信息同样会有
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, Object> rowData = new LinkedHashMap<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(resultSetMetaData.getColumnName(i).toUpperCase(), resultSet.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }
}
